package com.shiningstars.database;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.shiningstars.general.Config;
import com.shiningstars.general.Constants;
import com.shiningstars.valuebeans.Audit;
import com.shiningstars.valuebeans.Error;
import com.shiningstars.valuebeans.Feedback;
import com.shiningstars.valuebeans.MsLocation;

public class DatabaseHandler extends SQLiteOpenHelper implements Constants, Config {
	private static final String TABLE_AUDIT = "audit";
	private static final String TABLE_FEEDBACK = "feedback";
	private static final String TABLE_ERROR = "error";
	private static final String TABLE_MS_LOCATION = "ms_location";

	private static DatabaseHandler databaseHandler;

	private DatabaseHandler() {
		super(null, DATABASE_NAME, null, DATABASE_VERSION);
	}

	public static DatabaseHandler getInstance(Context context) {
		if (null == databaseHandler) {
			databaseHandler = new DatabaseHandler(context);
		}

		return databaseHandler;
	}

	private DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_AUDIT_TABLE = "CREATE TABLE IF NOT EXISTS audit (id INTEGER PRIMARY KEY, type TEXT, mobile_number TEXT, imei_number TEXT, description TEXT, version TEXT, created_date INTEGER)";
		String CREATE_FEEDBACK_TABLE = "CREATE TABLE IF NOT EXISTS feedback (id INTEGER PRIMARY KEY, feedback_id INTEGER, mobile_number TEXT, imei_number TEXT, created_date INTEGER, mode TEXT, is_synced TEXT, is_notified TEXT, is_read TEXT, question1 TEXT, question2 TEXT, question3 TEXT, question4 TEXT, question5 TEXT, question6 TEXT, question7 TEXT, question8 TEXT, question9 TEXT, question10 TEXT, question11 TEXT, question12 TEXT, question13 TEXT, question14 TEXT, question15 TEXT, question16 TEXT, question17 TEXT, question18 TEXT, question19 TEXT, question20 TEXT, question21 TEXT, question22 TEXT, question23 TEXT, question24 TEXT, question25 TEXT, question26 TEXT, question27 TEXT, question28 TEXT, question29 TEXT, question30 TEXT, question31 TEXT, question32 TEXT, question33 TEXT, question34 TEXT, question35 TEXT, question36 TEXT, question37 TEXT, question38 TEXT, question39 TEXT, question40 TEXT, question41 TEXT, question42 TEXT, question43 TEXT, question44 TEXT, question45 TEXT, question46 TEXT, question47 TEXT, question48 TEXT, question49 TEXT, question50 TEXT, question51 TEXT, question52 TEXT, question53 TEXT, question54 TEXT)";
		String CREATE_ERROR_TABLE = "CREATE TABLE IF NOT EXISTS error (id INTEGER PRIMARY KEY, mobile_number TEXT, imei_number TEXT, tag TEXT, description TEXT, created_date INTEGER)";
		String CREATE_MS_LOCATION_TABLE = "CREATE TABLE IF NOT EXISTS ms_location (id INTEGER PRIMARY KEY, ms_location_id INTEGER, place TEXT, stay_duration TEXT, contact_person TEXT, comments TEXT, posted_by TEXT, posted_on TEXT, is_synced TEXT, is_notified TEXT, created_date INTEGER)";

		db.execSQL(CREATE_AUDIT_TABLE);
		db.execSQL(CREATE_FEEDBACK_TABLE);
		db.execSQL(CREATE_ERROR_TABLE);
		db.execSQL(CREATE_MS_LOCATION_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String CREATE_MS_LOCATION_TABLE = "CREATE TABLE IF NOT EXISTS ms_location (id INTEGER PRIMARY KEY, ms_location_id INTEGER, place TEXT, stay_duration TEXT, contact_person TEXT, comments TEXT, posted_by TEXT, posted_on TEXT, is_synced TEXT, is_notified TEXT, created_date INTEGER)";
		String ALTER_FEEDBACK_TABLE_Q53 = "ALTER TABLE feedback ADD COLUMN question53 TEXT";
		String ALTER_FEEDBACK_TABLE_Q54 = "ALTER TABLE feedback ADD COLUMN question54 TEXT";

		db.execSQL(CREATE_MS_LOCATION_TABLE);
		db.execSQL(ALTER_FEEDBACK_TABLE_Q53);
		db.execSQL(ALTER_FEEDBACK_TABLE_Q54);
	}

	public long logAudit(Audit audit) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(TYPE, audit.getType());
		values.put(MOBILE_NUMBER, audit.getMobileNumber());
		values.put(IMEI_NUMBER, audit.getImeiNumber());
		values.put(DESCRIPTION, audit.getDescription());
		values.put(VERSION, audit.getVersion());
		values.put(CREATED_DATE, audit.getDate());

		long rowId = db.insert(TABLE_AUDIT, null, values);

		return rowId;
	}

	public Audit getAudit() {

		String query = "SELECT * FROM audit";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Audit audit = null;

		if (cursor.moveToFirst()) {
			audit = new Audit();

			audit.setId(cursor.getLong(0));
			audit.setType(cursor.getString(1));
			audit.setMobileNumber(cursor.getString(2));
			audit.setImeiNumber(cursor.getString(3));
			audit.setDescription(cursor.getString(4));
			audit.setVersion(cursor.getString(5));
			audit.setDate(cursor.getLong(6));
		}

		cursor.close();

		return audit;
	}

	public void deleteAudit(long auditId) {
		SQLiteDatabase db = getWritableDatabase();
		db.delete(TABLE_AUDIT, ID + " = ?", new String[] { String.valueOf(auditId) });

	}

	public long logError(Error error) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(MOBILE_NUMBER, error.getMobileNumber());
		values.put(IMEI_NUMBER, error.getImeiNumber());
		values.put(TAG, error.getTag());
		values.put(DESCRIPTION, error.getDescription());
		values.put(CREATED_DATE, error.getDate());

		long rowId = db.insert(TABLE_ERROR, null, values);

		return rowId;
	}

	public Error getError() {

		String query = "SELECT * FROM error";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Error error = null;

		if (cursor.moveToFirst()) {
			error = new Error();

			error.setId(cursor.getLong(0));
			error.setMobileNumber(cursor.getString(1));
			error.setImeiNumber(cursor.getString(2));
			error.setTag(cursor.getString(3));
			error.setDescription(cursor.getString(4));
			error.setDate(cursor.getLong(5));
		}

		cursor.close();

		return error;
	}

	public void deleteError(long errorId) {
		SQLiteDatabase db = getWritableDatabase();
		db.delete(TABLE_ERROR, ID + " = ?", new String[] { String.valueOf(errorId) });

	}

	public long saveFeedback(Feedback feedback) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(FEEDBACK_ID, feedback.getFeedbackId());
		values.put(MOBILE_NUMBER, feedback.getMobileNumber());
		values.put(IMEI_NUMBER, feedback.getImeiNumber());
		values.put(CREATED_DATE, Calendar.getInstance().getTimeInMillis());
		values.put(MODE, feedback.getMode());
		values.put(IS_SYNCED, feedback.getIsSynced());
		values.put(IS_NOTIFIED, feedback.getIsNotified());
		values.put(IS_READ, feedback.getIsRead());
		values.put(QUESTION + "1", feedback.getQuestion1());
		values.put(QUESTION + "2", feedback.getQuestion2());
		values.put(QUESTION + "3", feedback.getQuestion3());
		values.put(QUESTION + "4", feedback.getQuestion4());
		values.put(QUESTION + "5", feedback.getQuestion5());
		values.put(QUESTION + "6", feedback.getQuestion6());
		values.put(QUESTION + "7", feedback.getQuestion7());
		values.put(QUESTION + "8", feedback.getQuestion8());
		values.put(QUESTION + "9", feedback.getQuestion9());
		values.put(QUESTION + "10", feedback.getQuestion10());
		values.put(QUESTION + "11", feedback.getQuestion11());
		values.put(QUESTION + "12", feedback.getQuestion12());
		values.put(QUESTION + "13", feedback.getQuestion13());
		values.put(QUESTION + "14", feedback.getQuestion14());
		values.put(QUESTION + "15", feedback.getQuestion15());
		values.put(QUESTION + "16", feedback.getQuestion16());
		values.put(QUESTION + "17", feedback.getQuestion17());
		values.put(QUESTION + "18", feedback.getQuestion18());
		values.put(QUESTION + "19", feedback.getQuestion19());
		values.put(QUESTION + "20", feedback.getQuestion20());
		values.put(QUESTION + "21", feedback.getQuestion21());
		values.put(QUESTION + "22", feedback.getQuestion22());
		values.put(QUESTION + "23", feedback.getQuestion23());
		values.put(QUESTION + "24", feedback.getQuestion24());
		values.put(QUESTION + "25", feedback.getQuestion25());
		values.put(QUESTION + "26", feedback.getQuestion26());
		values.put(QUESTION + "27", feedback.getQuestion27());
		values.put(QUESTION + "28", feedback.getQuestion28());
		values.put(QUESTION + "29", feedback.getQuestion29());
		values.put(QUESTION + "30", feedback.getQuestion30());
		values.put(QUESTION + "31", feedback.getQuestion31());
		values.put(QUESTION + "32", feedback.getQuestion32());
		values.put(QUESTION + "33", feedback.getQuestion33());
		values.put(QUESTION + "34", feedback.getQuestion34());
		values.put(QUESTION + "35", feedback.getQuestion35());
		values.put(QUESTION + "36", feedback.getQuestion36());
		values.put(QUESTION + "37", feedback.getQuestion37());
		values.put(QUESTION + "38", feedback.getQuestion38());
		values.put(QUESTION + "39", feedback.getQuestion39());
		values.put(QUESTION + "40", feedback.getQuestion40());
		values.put(QUESTION + "41", feedback.getQuestion41());
		values.put(QUESTION + "42", feedback.getQuestion42());
		values.put(QUESTION + "43", feedback.getQuestion43());
		values.put(QUESTION + "44", feedback.getQuestion44());
		values.put(QUESTION + "45", feedback.getQuestion45());
		values.put(QUESTION + "46", feedback.getQuestion46());
		values.put(QUESTION + "47", feedback.getQuestion47());
		values.put(QUESTION + "48", feedback.getQuestion48());
		values.put(QUESTION + "49", feedback.getQuestion49());
		values.put(QUESTION + "50", feedback.getQuestion50());
		values.put(QUESTION + "51", feedback.getQuestion51());
		values.put(QUESTION + "52", feedback.getQuestion52());
		values.put(QUESTION + "53", feedback.getQuestion53());
		values.put(QUESTION + "54", feedback.getQuestion54());

		long rowId = db.insert(TABLE_FEEDBACK, null, values);

		return rowId;
	}

	public Feedback getFeedbackToSend() {

		String query = "SELECT * FROM feedback WHERE feedback_id = 0 AND is_synced = 'N' AND is_notified = 'N'";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Feedback feedback = null;

		if (cursor.moveToFirst()) {
			feedback = new Feedback();

			feedback.setId(cursor.getLong(0));
			feedback.setFeedbackId(cursor.getLong(1));
			feedback.setMobileNumber(cursor.getString(2));
			feedback.setImeiNumber(cursor.getString(3));
			feedback.setDate(cursor.getLong(4));
			feedback.setMode(cursor.getString(5));
			feedback.setIsSynced(Y);
			feedback.setIsNotified(cursor.getString(7));
			feedback.setIsRead(cursor.getString(8));
			feedback.setQuestion1(cursor.getString(9));
			feedback.setQuestion2(cursor.getString(10));
			feedback.setQuestion3(cursor.getString(11));
			feedback.setQuestion4(cursor.getString(12));
			feedback.setQuestion5(cursor.getString(13));
			feedback.setQuestion6(cursor.getString(14));
			feedback.setQuestion7(cursor.getString(15));
			feedback.setQuestion8(cursor.getString(16));
			feedback.setQuestion9(cursor.getString(17));
			feedback.setQuestion10(cursor.getString(18));
			feedback.setQuestion11(cursor.getString(19));
			feedback.setQuestion12(cursor.getString(20));
			feedback.setQuestion13(cursor.getString(21));
			feedback.setQuestion14(cursor.getString(22));
			feedback.setQuestion15(cursor.getString(23));
			feedback.setQuestion16(cursor.getString(24));
			feedback.setQuestion17(cursor.getString(25));
			feedback.setQuestion18(cursor.getString(26));
			feedback.setQuestion19(cursor.getString(27));
			feedback.setQuestion20(cursor.getString(28));
			feedback.setQuestion21(cursor.getString(29));
			feedback.setQuestion22(cursor.getString(30));
			feedback.setQuestion23(cursor.getString(31));
			feedback.setQuestion24(cursor.getString(32));
			feedback.setQuestion25(cursor.getString(33));
			feedback.setQuestion26(cursor.getString(34));
			feedback.setQuestion27(cursor.getString(35));
			feedback.setQuestion28(cursor.getString(36));
			feedback.setQuestion29(cursor.getString(37));
			feedback.setQuestion30(cursor.getString(38));
			feedback.setQuestion31(cursor.getString(39));
			feedback.setQuestion32(cursor.getString(40));
			feedback.setQuestion33(cursor.getString(41));
			feedback.setQuestion34(cursor.getString(42));
			feedback.setQuestion35(cursor.getString(43));
			feedback.setQuestion36(cursor.getString(44));
			feedback.setQuestion37(cursor.getString(45));
			feedback.setQuestion38(cursor.getString(46));
			feedback.setQuestion39(cursor.getString(47));
			feedback.setQuestion40(cursor.getString(48));
			feedback.setQuestion41(cursor.getString(49));
			feedback.setQuestion42(cursor.getString(50));
			feedback.setQuestion43(cursor.getString(51));
			feedback.setQuestion44(cursor.getString(52));
			feedback.setQuestion45(cursor.getString(53));
			feedback.setQuestion46(cursor.getString(54));
			feedback.setQuestion47(cursor.getString(55));
			feedback.setQuestion48(cursor.getString(56));
			feedback.setQuestion49(cursor.getString(57));
			feedback.setQuestion50(cursor.getString(58));
			feedback.setQuestion51(cursor.getString(59));
			feedback.setQuestion52(cursor.getString(60));
			feedback.setQuestion53(cursor.getString(61));
			feedback.setQuestion54(cursor.getString(62));
		}

		cursor.close();

		return feedback;
	}

	public Feedback getFeedback(long id) {

		String query = "SELECT * FROM feedback WHERE id = " + id;

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Feedback feedback = null;

		if (cursor.moveToFirst()) {
			feedback = new Feedback();

			feedback.setId(cursor.getLong(0));
			feedback.setFeedbackId(cursor.getLong(1));
			feedback.setMobileNumber(cursor.getString(2));
			feedback.setImeiNumber(cursor.getString(3));
			feedback.setDate(cursor.getLong(4));
			feedback.setMode(cursor.getString(5));
			feedback.setIsSynced(cursor.getString(6));
			feedback.setIsNotified(cursor.getString(7));
			feedback.setIsRead(cursor.getString(8));
			feedback.setQuestion1(cursor.getString(9));
			feedback.setQuestion2(cursor.getString(10));
			feedback.setQuestion3(cursor.getString(11));
			feedback.setQuestion4(cursor.getString(12));
			feedback.setQuestion5(cursor.getString(13));
			feedback.setQuestion6(cursor.getString(14));
			feedback.setQuestion7(cursor.getString(15));
			feedback.setQuestion8(cursor.getString(16));
			feedback.setQuestion9(cursor.getString(17));
			feedback.setQuestion10(cursor.getString(18));
			feedback.setQuestion11(cursor.getString(19));
			feedback.setQuestion12(cursor.getString(20));
			feedback.setQuestion13(cursor.getString(21));
			feedback.setQuestion14(cursor.getString(22));
			feedback.setQuestion15(cursor.getString(23));
			feedback.setQuestion16(cursor.getString(24));
			feedback.setQuestion17(cursor.getString(25));
			feedback.setQuestion18(cursor.getString(26));
			feedback.setQuestion19(cursor.getString(27));
			feedback.setQuestion20(cursor.getString(28));
			feedback.setQuestion21(cursor.getString(29));
			feedback.setQuestion22(cursor.getString(30));
			feedback.setQuestion23(cursor.getString(31));
			feedback.setQuestion24(cursor.getString(32));
			feedback.setQuestion25(cursor.getString(33));
			feedback.setQuestion26(cursor.getString(34));
			feedback.setQuestion27(cursor.getString(35));
			feedback.setQuestion28(cursor.getString(36));
			feedback.setQuestion29(cursor.getString(37));
			feedback.setQuestion30(cursor.getString(38));
			feedback.setQuestion31(cursor.getString(39));
			feedback.setQuestion32(cursor.getString(40));
			feedback.setQuestion33(cursor.getString(41));
			feedback.setQuestion34(cursor.getString(42));
			feedback.setQuestion35(cursor.getString(43));
			feedback.setQuestion36(cursor.getString(44));
			feedback.setQuestion37(cursor.getString(45));
			feedback.setQuestion38(cursor.getString(46));
			feedback.setQuestion39(cursor.getString(47));
			feedback.setQuestion40(cursor.getString(48));
			feedback.setQuestion41(cursor.getString(49));
			feedback.setQuestion42(cursor.getString(50));
			feedback.setQuestion43(cursor.getString(51));
			feedback.setQuestion44(cursor.getString(52));
			feedback.setQuestion45(cursor.getString(53));
			feedback.setQuestion46(cursor.getString(54));
			feedback.setQuestion47(cursor.getString(55));
			feedback.setQuestion48(cursor.getString(56));
			feedback.setQuestion49(cursor.getString(57));
			feedback.setQuestion50(cursor.getString(58));
			feedback.setQuestion51(cursor.getString(59));
			feedback.setQuestion52(cursor.getString(60));
			feedback.setQuestion53(cursor.getString(61));
			feedback.setQuestion54(cursor.getString(62));
		}

		cursor.close();

		return feedback;
	}

	public Feedback getFeedbackToNotify() {

		String query = "SELECT id, feedback_id FROM feedback WHERE feedback_id != 0 AND is_synced = 'Y' AND is_notified = 'N'";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Feedback feedback = null;

		if (cursor.moveToFirst()) {
			feedback = new Feedback();

			feedback.setId(cursor.getLong(0));
			feedback.setFeedbackId(cursor.getLong(1));
			feedback.setIsNotified(P);
		}

		cursor.close();

		return feedback;
	}

	public Feedback getFeedbackToSyncNotify() {

		String query = "SELECT id, feedback_id, question3 FROM feedback WHERE is_notified = 'P'";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Feedback feedback = null;

		if (cursor.moveToFirst()) {
			feedback = new Feedback();

			feedback.setId(cursor.getLong(0));
			feedback.setFeedbackId(cursor.getLong(1));
			feedback.setIsNotified(Y);
			feedback.setQuestion3(cursor.getString(2));
		}

		cursor.close();

		return feedback;
	}

	public List<Feedback> getFeedbackHistory(String mobileNumber) {
		List<Feedback> feedbackHistory = new ArrayList<Feedback>();
		feedbackHistory.add(new Feedback());

		String query = "SELECT id, is_notified, question3 FROM feedback WHERE mobile_number = '" + mobileNumber
				+ "' ORDER BY created_date DESC";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			do {
				Feedback feedback = new Feedback();
				feedback.setId(cursor.getLong(0));
				feedback.setIsNotified(cursor.getString(1));
				feedback.setQuestion3(cursor.getString(2));

				feedbackHistory.add(feedback);
			} while (cursor.moveToNext());
		}

		cursor.close();

		return feedbackHistory;
	}

	public int updateFeedbackIsSync(long id, long feedbackId) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(FEEDBACK_ID, feedbackId);
		values.put(IS_SYNCED, Y);

		int count = db.update(TABLE_FEEDBACK, values, ID + " = ?", new String[] { String.valueOf(id) });

		return count;
	}

	public int updateFeedbackIsNotified(long id, String isNotified) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(IS_NOTIFIED, isNotified);

		int count = db.update(TABLE_FEEDBACK, values, ID + " = ?", new String[] { String.valueOf(id) });

		return count;
	}

	public List<Feedback> getFeedbackList(String centerName) {

		String query = "SELECT * FROM feedback WHERE question1 = '" + centerName + "'";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		List<Feedback> feedbackList = new ArrayList<Feedback>();

		if (cursor.moveToFirst()) {
			do {
				Feedback feedback = new Feedback();

				feedback.setId(cursor.getLong(0));
				feedback.setFeedbackId(cursor.getLong(1));
				feedback.setMobileNumber(cursor.getString(2));
				feedback.setImeiNumber(cursor.getString(3));
				feedback.setDate(cursor.getLong(4));
				feedback.setMode(cursor.getString(5));
				feedback.setIsSynced(cursor.getString(6));
				feedback.setIsNotified(cursor.getString(7));
				feedback.setIsRead(cursor.getString(8));
				feedback.setQuestion1(cursor.getString(9));
				feedback.setQuestion2(cursor.getString(10));
				feedback.setQuestion3(cursor.getString(11));
				feedback.setQuestion4(cursor.getString(12));
				feedback.setQuestion5(cursor.getString(13));
				feedback.setQuestion6(cursor.getString(14));
				feedback.setQuestion7(cursor.getString(15));
				feedback.setQuestion8(cursor.getString(16));
				feedback.setQuestion9(cursor.getString(17));
				feedback.setQuestion10(cursor.getString(18));
				feedback.setQuestion11(cursor.getString(19));
				feedback.setQuestion12(cursor.getString(20));
				feedback.setQuestion13(cursor.getString(21));
				feedback.setQuestion14(cursor.getString(22));
				feedback.setQuestion15(cursor.getString(23));
				feedback.setQuestion16(cursor.getString(24));
				feedback.setQuestion17(cursor.getString(25));
				feedback.setQuestion18(cursor.getString(26));
				feedback.setQuestion19(cursor.getString(27));
				feedback.setQuestion20(cursor.getString(28));
				feedback.setQuestion21(cursor.getString(29));
				feedback.setQuestion22(cursor.getString(30));
				feedback.setQuestion23(cursor.getString(31));
				feedback.setQuestion24(cursor.getString(32));
				feedback.setQuestion25(cursor.getString(33));
				feedback.setQuestion26(cursor.getString(34));
				feedback.setQuestion27(cursor.getString(35));
				feedback.setQuestion28(cursor.getString(36));
				feedback.setQuestion29(cursor.getString(37));
				feedback.setQuestion30(cursor.getString(38));
				feedback.setQuestion31(cursor.getString(39));
				feedback.setQuestion32(cursor.getString(40));
				feedback.setQuestion33(cursor.getString(41));
				feedback.setQuestion34(cursor.getString(42));
				feedback.setQuestion35(cursor.getString(43));
				feedback.setQuestion36(cursor.getString(44));
				feedback.setQuestion37(cursor.getString(45));
				feedback.setQuestion38(cursor.getString(46));
				feedback.setQuestion39(cursor.getString(47));
				feedback.setQuestion40(cursor.getString(48));
				feedback.setQuestion41(cursor.getString(49));
				feedback.setQuestion42(cursor.getString(50));
				feedback.setQuestion43(cursor.getString(51));
				feedback.setQuestion44(cursor.getString(52));
				feedback.setQuestion45(cursor.getString(53));
				feedback.setQuestion46(cursor.getString(54));
				feedback.setQuestion47(cursor.getString(55));
				feedback.setQuestion48(cursor.getString(56));
				feedback.setQuestion49(cursor.getString(57));
				feedback.setQuestion50(cursor.getString(58));
				feedback.setQuestion51(cursor.getString(59));
				feedback.setQuestion52(cursor.getString(60));
				feedback.setQuestion53(cursor.getString(61));
				feedback.setQuestion54(cursor.getString(62));

				feedbackList.add(feedback);
			} while (cursor.moveToNext());
		}

		cursor.close();

		return feedbackList;
	}

	public List<Long> getFeedbackIdList(String centerNames) {

		String query = "SELECT feedback_id FROM feedback WHERE question1 IN (" + centerNames + ")";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		List<Long> feedbackIdList = new ArrayList<Long>();

		if (cursor.moveToFirst()) {
			do {
				feedbackIdList.add(cursor.getLong(0));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return feedbackIdList;
	}

	public int updateFeedbackIsRead(long id) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(IS_READ, Y);

		int count = db.update(TABLE_FEEDBACK, values, ID + " = ?", new String[] { String.valueOf(id) });

		return count;
	}

	public String getUnreadFlag(String centerName) {
		String query = "SELECT is_read FROM feedback WHERE question1 = '" + centerName + "' AND is_read = 'N'";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		String unreadFlag = null;

		if (cursor.moveToFirst() && cursor.getString(0).equals(N)) {
			unreadFlag = Y;
		}

		cursor.close();

		return unreadFlag;
	}

	public MsLocation getMsLocation() {
		String query = "SELECT * FROM ms_location";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		MsLocation msLocation = null;

		if (cursor.moveToFirst()) {
			msLocation = new MsLocation();

			msLocation.setId(cursor.getLong(0));
			msLocation.setMsLocationId(cursor.getLong(1));
			msLocation.setPlace(cursor.getString(2));
			msLocation.setStayDuration(cursor.getString(3));
			msLocation.setContactPerson(cursor.getString(4));
			msLocation.setComments(cursor.getString(5));
			msLocation.setPostedBy(cursor.getString(6));
			msLocation.setPostedOn(cursor.getString(7));
			msLocation.setIsSynced(cursor.getString(8));
			msLocation.setIsNotified(cursor.getString(9));
			msLocation.setCreatedDate(cursor.getLong(10));
		}

		cursor.close();

		return msLocation;
	}

	public long updateMsLocation(MsLocation msLocation) {

		MsLocation location = getMsLocation();

		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(MS_LOCATION_ID, msLocation.getMsLocationId());
		values.put(PLACE, msLocation.getPlace());
		values.put(STAY_DURATION, msLocation.getStayDuration());
		values.put(CONTACT_PERSON, msLocation.getContactPerson());
		values.put(COMMENTS, msLocation.getComments());
		values.put(POSTED_BY, msLocation.getPostedBy());
		values.put(POSTED_ON, msLocation.getPostedOn());
		values.put(IS_SYNCED, msLocation.getIsSynced());
		values.put(IS_NOTIFIED, msLocation.getIsNotified());
		values.put(CREATED_DATE, msLocation.getCreatedDate());

		long count = -1;

		if (null == location) {
			count = db.insert(TABLE_MS_LOCATION, null, values);
		} else {
			count = db
					.update(TABLE_MS_LOCATION, values, ID + " = ?", new String[] { String.valueOf(location.getId()) });
		}

		return count;
	}

	public int updateMsLocationIsSync(long id, long msLocationId) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(MS_LOCATION_ID, msLocationId);
		values.put(IS_SYNCED, Y);

		int count = db.update(TABLE_MS_LOCATION, values, ID + " = ?", new String[] { String.valueOf(id) });

		return count;
	}

	public int updateMsLocationIsNotified(long id, String isNotified) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(IS_NOTIFIED, isNotified);

		int count = db.update(TABLE_MS_LOCATION, values, ID + " = ?", new String[] { String.valueOf(id) });

		return count;
	}

	public Map<String, String> getTeacherNamesWhoSentFeedback(String date1, String date2) {
		String query = "SELECT * FROM feedback WHERE question3 IN ('" + date1 + "', '" + date2 + "')";

		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Map<String, String> feedbackSentMap = new HashMap<String, String>();

		if (cursor.moveToFirst()) {
			do {
				feedbackSentMap.put(cursor.getString(2), cursor.getString(10));
			} while (cursor.moveToNext());
		}

		cursor.close();

		return feedbackSentMap;
	}
}